﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using KVB.Common;

namespace Customer_Profile.eCard.DBDao
{
    public class RewardDao
    {
        public static DataSet RewardSearch(string entity_id,string reward_status,string start_date,string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@entity", SqlDbType.VarChar,50),
                                             new SqlParameter("@reward_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = entity_id;
                parameters[1].Value = reward_status;
                parameters[2].Value = start_date;
                parameters[3].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_Search", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.RewardSearch", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardDetailByBatchID(int reward_batch_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@reward_batch_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = reward_batch_id;                
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_DealsDetail", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardDetailByBatchID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardDetailByRewardID(int reward_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@reward_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = reward_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_RewardDetail_byRewardID", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardDetailByBatchID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardDetailByALL(string start_date,string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_DealsDetail_ALL", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardDetailByALL", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardPayDetailByBatchID(int reward_batch_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@reward_batch_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = reward_batch_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_RewardPay_DealsDetail", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardDetailByBatchID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void RewardUpbdateAccountInfoByBatchID(int reward_batch_id,
            string year,string month,string account_name,string staff_no,string reward_bankaccount,string remark)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={                                               
                                             new SqlParameter("@reward_batch_id", SqlDbType.Int) ,
                                             new SqlParameter("@year", SqlDbType.VarChar,250) ,
                                             new SqlParameter("@month", SqlDbType.VarChar,250) ,
                                             new SqlParameter("@account_name", SqlDbType.VarChar,250) ,
                                             new SqlParameter("@staff_no", SqlDbType.VarChar,250) ,
                                             new SqlParameter("@reward_bankaccount", SqlDbType.VarChar,250) ,
                                             new SqlParameter("@remark", SqlDbType.VarChar,560) 
                                           };
                parameters[0].Value = reward_batch_id;
                parameters[1].Value = year;
                parameters[2].Value = month;
                parameters[3].Value = account_name;
                parameters[4].Value = staff_no;
                parameters[5].Value = reward_bankaccount;
                parameters[6].Value = remark;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "[eCard_Reward_AccountInfo_Update]", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardUpbdateAccountInfoByBatchID", ex.Message);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet RewardPaySumAmount(int reward_batch_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@reward_batch_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = reward_batch_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_ActualAmount", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardPaySumAmount", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardAccountInfo(int reward_batch_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@reward_batch_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = reward_batch_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_AccountInfo", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardPaySumAmount", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet ExtendOriginalDetailByExetndID(int extend_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@extend_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = extend_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_ExtendOriginalDeal", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.ExtendOriginalDetailByExetndID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet SettleTransferDetail(int settletransfer_id)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@settletransfer_id", SqlDbType.VarChar,50)                                            
                                           };
                parameters[0].Value = settletransfer_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_ExtendOriginalDeal", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.ExtendOriginalDetailByExetndID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardList(string entity_id, string reward_status, string start_date, string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@entity", SqlDbType.VarChar,50),
                                             new SqlParameter("@reward_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = entity_id;
                parameters[1].Value = reward_status;
                parameters[2].Value = start_date;
                parameters[3].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_List", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardDetail_Search(string entity_id, string reward_status, string start_date, string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@entity", SqlDbType.VarChar,50),
                                             new SqlParameter("@reward_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = entity_id;
                parameters[1].Value = reward_status;
                parameters[2].Value = start_date;
                parameters[3].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Reward_DetailSearch", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet RewardList_IandV(string entity_id,string start_date, string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@entity", SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = entity_id;                
                parameters[1].Value = start_date;
                parameters[2].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_RewardIV_List", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        //eCard_reward_insert
        public static bool RewardInsert(string dealno_list,string entity_id,string entity,decimal dreward_amount,string year,string month,string reward_bankaccount,
            string staff_no,string account_name,
            string userid,string username,string remark)
        {
            bool bok = false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@dealno_list", SqlDbType.VarChar,8000),
                                             new SqlParameter("@entity_id", SqlDbType.VarChar,250),
                                             new SqlParameter("@entity", SqlDbType.VarChar,250),
                                             new SqlParameter("@reward_amount", SqlDbType.Decimal),
                                             new SqlParameter("@year", SqlDbType.VarChar,50),
                                             new SqlParameter("@month", SqlDbType.VarChar,50),
                                             new SqlParameter("@reward_bankaccount", SqlDbType.VarChar,250),
                                             new SqlParameter("@staff_no", SqlDbType.VarChar,250),
                                             new SqlParameter("@account_name", SqlDbType.VarChar,250),
                                             new SqlParameter("@userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@username", SqlDbType.VarChar,50),
                                             new SqlParameter("@remark", SqlDbType.VarChar,500)
                                           };
                parameters[0].Value = dealno_list;
                parameters[1].Value = entity_id;
                parameters[2].Value = entity;
                parameters[3].Value = dreward_amount;
                parameters[4].Value = year;
                parameters[5].Value = month;
                parameters[6].Value = reward_bankaccount;

                parameters[7].Value = staff_no;
                parameters[8].Value = account_name;

                parameters[9].Value = userid;
                parameters[10].Value = username;
                parameters[11].Value = remark;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_reward_insert", parameters);
                bok = true;
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardInsert", ex.Message);
                bok = false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        public static bool RewardPay(int ibatch_id,decimal dActualAmount, string userid, string username)
        {
            bool bok = false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
                                             new SqlParameter("@batch_id", SqlDbType.Int),
                                             new SqlParameter("@actual_amount", SqlDbType.Decimal),
                                             new SqlParameter("@userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@username", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = ibatch_id;
                parameters[1].Value = dActualAmount;
                parameters[2].Value = userid;
                parameters[3].Value = username;
                
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_reward_pay", parameters);
                bok = true;
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardPay", ex.Message);
                bok = false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }
    }
}